using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
using Commons.Models.Definitions;
using Commons.Models.Scores;
using Commons.Models.Settings;
using Commons.Preferences;

namespace Commons.Daos.Scores
{
	public class HighScoreDao : SQLDao<Dictionary<int, HighScore>>, IHighScoreDao
	{
		private const string CreateSql = "create table highscore (user_id integer, code integer, current_score integer, last_score integer, best_score integer, last_update integer, primary key (user_id, code))";
		private const string SelectSql = "select code, current_score, last_score, best_score, last_update from highscore where user_id = ?";
        private const string InsertSql = "insert into highscore (user_id, code, current_score, last_score, best_score, last_update) OUTPUT INSERTED.ID values (?, ?, ?, ?, ?, ?)";
		private const string UpdateSql = "update highscore set current_score = ?, last_score = ?, best_score = ?, last_update = ? where user_id = ? and code = ?";
		private const string DeleteAllSql = "delete from highscore where user_id = ?";

		protected override IEnumerable<string> GetUpgradeSqls(int oldVersion)
		{
			if (oldVersion == 0)
			{
				yield return CreateSql;
			}
		}

		public IEnumerable<HighScore> GetAll()
		{
			if (cache == null)
			{
				SqlParameter userId = new SqlParameter("userId", GamePrefs.UserId);

				cache = DaoSupport.QueryForDictionary<int, HighScore>(new HighScoreRowMapper(), 0, SelectSql, userId);
			}

			return cache.Values;
		}

		public HighScore Get(int code)
		{
			GetAll();

			HighScore highScore;

			return cache.TryGetValue(code, out highScore) ? highScore : null;
		}

		public void Insert(HighScore highScore)
		{
			SqlParameter userId = new SqlParameter("userId", GamePrefs.UserId);
			SqlParameter code = new SqlParameter("code", highScore.Definition.Code);
			SqlParameter currentScore = new SqlParameter("currentScore", highScore.CurrentScore);
			SqlParameter lastScore = new SqlParameter("lastScore", highScore.LastScore);
			SqlParameter bestScore = new SqlParameter("bestScore", highScore.BestScore);
			SqlParameter lastUpdate = new SqlParameter("lastUpdate", highScore.LastUpdate.ToUniversalTime().Ticks);

			DaoSupport.Insert(InsertSql, userId, code, currentScore, lastScore, bestScore, lastUpdate);

			if (cache != null)
			{
				cache.Add(highScore.Definition.Code, highScore);
			}
		}

		public int Update(HighScore highScore)
		{
			SqlParameter currentScore = new SqlParameter("currentScore", highScore.CurrentScore);
			SqlParameter lastScore = new SqlParameter("lastScore", highScore.LastScore);
			SqlParameter bestScore = new SqlParameter("bestScore", highScore.BestScore);
			SqlParameter lastUpdate = new SqlParameter("lastUpdate", highScore.LastUpdate.ToUniversalTime().Ticks);
			SqlParameter userId = new SqlParameter("userId", GamePrefs.UserId);
			SqlParameter code = new SqlParameter("code", highScore.Definition.Code);

			int rows = DaoSupport.Update(UpdateSql, currentScore, lastScore, bestScore, lastUpdate, userId, code);

			if (rows > 0 && cache != null)
			{
				cache[highScore.Definition.Code] = highScore;
			}

			return rows;
		}

		public void DeleteAll()
		{
			SqlParameter userId = new SqlParameter("userId", GamePrefs.UserId);

			DaoSupport.Delete(DeleteAllSql, userId);

			cache = null;
		}

		public class HighScoreRowMapper : IRowMapper<HighScore>
		{
			private HighScoreSettings highScoreSettings = SettingsModel.Instance.GetSettings<HighScoreSettings>();

			public HighScore MapRow(DbDataReader reader)
			{
				HighScoreDefinition definition = highScoreSettings.GetDefinition(reader.GetInt32(0));
				int currentScore = reader.GetInt32(1);
				int lastScore = reader.GetInt32(2);
				int bestScore = reader.GetInt32(3);
				DateTime lastUpdate = new DateTime(reader.GetInt64(4), DateTimeKind.Utc);

				return new HighScore(definition, lastUpdate, currentScore, lastScore, bestScore);
			}
		}
	}
}